# -*- coding: utf-8 -*-
# @Time: 2024/10/25 12:01
# @Author: foxhuty
# @File: good_scores.py
import pandas as pd
import numpy as np

pd.set_option('display.max_columns', 100)


def get_average(df):
    subjects_av = [col for col in df.columns if
                   col in ['语文', '数学', '英语', '物理', '历史', '政治', '地理', '生物', '化学', '总分']]
    get_av = df.groupby('班级')[subjects_av].mean().round(2)

    print(get_av)
    av_general = df[subjects_av].apply(np.nanmean, axis=0)
    av_percentage = get_av / av_general.round(2)

    av_percentage = av_percentage.applymap(lambda x: format(x, '.2%'))
    # av_percentage = av_percentage.applymap(lambda x: x if pd.notna(x) else '')  # 如果不是NaN，则保持原值，否则替换为0
    av_percentage = av_percentage.applymap(lambda x: x.replace('nan%', ''))  # 不显示nan%
    # print(av_percentage)
    av_percentage.rename(
        columns={'语文': '语文占比', '数学': '数学占比', '英语': '英语占比', '物理': '物理占比', '历史': '历史占比',
                 '政治': '政治占比', '地理': '地理占比', '化学': '化学占比', '生物': '生物占比', '总分': '总分占比'},
        inplace=True)
    # print(av_percentage)
    # av_general.name = '年级平均分'
    # av_scores = av_class.append(av_general)
    get_av.loc['年级平均'] = av_general
    get_av['参考人数'] = df['班级'].value_counts()
    get_av.loc['年级平均', '参考人数'] = get_av['参考人数'].sum()
    # columns = ['参考人数', '语文', '数学', '英语', '物理', '历史', '政治', '地理', '生物', '化学', '总分']
    # get_av = get_av[columns]
    # print(get_av.columns.tolist())
    # print(get_av.round(2))
    final_av_percentage = pd.concat([get_av, av_percentage], axis=1)
    columns = ['参考人数', '语文', '语文占比', '数学', '数学占比', '英语', '英语占比', '物理', '物理占比', '历史',
               '历史占比', '政治', '政治占比', '地理', '地理占比', '生物', '生物占比', '化学', '化学占比', '总分',
               '总分占比']
    final_av_percentage = final_av_percentage[columns]
    print(final_av_percentage)
    final_av_percentage.to_excel('平均分统计.xlsx', float_format='%.2f')
    return get_av


def good_scores(df, chn, math, eng, phys, his, chem, bio, pol, geo, total):
    single_bio, single_chem, single_chn, single_eng, single_geo, single_his, single_math, single_phys, single_pol, single_total = get_good_score_subject(
        bio, chem, chn, df, eng, geo, his, math, phys, pol, total)

    name_num = df.groupby(['班级'])['姓名'].count()
    name_num.name = '参考人数'

    df2 = df[df['总分'] >= total]
    double_chn = df2[df2['语文'] >= chn].groupby(['班级'])['语文'].count()
    double_math = df2[df2['数学'] >= math].groupby(['班级'])['数学'].count()
    double_eng = df2[df2['英语'] >= eng].groupby(['班级'])['英语'].count()
    double_pol = df2[df2['政治'] >= pol].groupby(['班级'])['政治'].count()
    double_his = df2[df2['历史'] >= his].groupby(['班级'])['历史'].count()
    double_geo = df2[df2['地理'] >= geo].groupby(['班级'])['地理'].count()
    double_phys = df2[df2['物理'] >= phys].groupby(['班级'])['物理'].count()
    double_chem = df2[df2['化学'] >= chem].groupby(['班级'])['化学'].count()
    double_bio = df2[df2['生物'] >= bio].groupby(['班级'])['生物'].count()
    double_total = df2[df2['总分'] >= total].groupby(['班级'])['总分'].count()

    goodscore_dict = {'参考人数': ' ', '语文': chn, '数学': math, '英语': eng, '政治': pol, '历史': his,
                      '地理': geo,
                      '物理': phys, '化学': chem, '生物': bio, '总分': total}
    goodscore_df = pd.DataFrame(goodscore_dict, index=['有效分数'])

    result_single = pd.concat([name_num, single_chn, single_math, single_eng,
                               single_pol, single_his, single_geo,
                               single_phys, single_chem, single_bio, single_total],
                              axis=1)
    result_double = pd.concat(
        [name_num, double_chn, double_math, double_eng,
         double_pol, double_his, double_geo,
         double_phys, double_chem, double_bio, double_total], axis=1)


def get_good_score_subject(bio, chem, chn, df, eng, geo, his, math, phys, pol, total):
    single_chn = single_subject_num(chn, df)
    single_math = df[df['数学'] >= math].groupby(['班级'])['数学'].count()
    single_eng = df[df['英语'] >= eng].groupby(['班级'])['英语'].count()
    single_pol = df[df['政治'] >= pol].groupby(['班级'])['政治'].count()
    single_his = df[df['历史'] >= his].groupby(['班级'])['历史'].count()
    single_geo = df[df['地理'] >= geo].groupby(['班级'])['地理'].count()
    single_phys = df[df['物理'] >= phys].groupby(['班级'])['物理'].count()
    single_chem = df[df['化学'] >= chem].groupby(['班级'])['化学'].count()
    single_bio = df[df['生物'] >= bio].groupby(['班级'])['生物'].count()
    single_total = df[df['总分'] >= total].groupby(['班级'])['总分'].count()
    return single_bio, single_chem, single_chn, single_eng, single_geo, single_his, single_math, single_phys, single_pol, single_total


def single_subject_num(chn, df):
    single_chn = df[df['语文'] >= chn].groupby(['班级'])['语文'].count()
    return single_chn


if __name__ == '__main__':
    file_path = r'D:\data_test\高2026级学生10月考成绩汇总.xlsx'
    df = pd.read_excel(file_path, sheet_name=1)
    single_subject_data=df[df['语文']>=80].groupby('班级')['语文'].count()
    # single_subject_num=df.groupby('班级')['语文'].count()
    print(single_subject_data)
